package com.xxxy.jsq.dao;

import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Record;
import com.xxxy.jsq.model.Page;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class RecordDao implements BaseDao<Record> {
    private C3P0Utils db = new C3P0Utils();

    @Override
    public String add(Record model) {
        Connection conn = db.getConnection();
        String sql = "INSERT INTO pa_record(record_name, record_date, record_memo, record_people, record_pid, record_flag) VALUES(?,?,?,?,?,?)";
        
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setString(1, model.getRecordName());
            pstm.setDate(2, new Date(model.getRecordDate().getTime()));
            pstm.setString(3, model.getRecordMemo());
            pstm.setString(4, model.getRecordPeople());
            pstm.setLong(5, model.getRecordPid());
            pstm.setString(6, model.getRecordFlag());

            return pstm.executeUpdate() > 0 ? "Ok" : "Err";
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
    }

    @Override
    public String update(Record model) {
        Connection conn = db.getConnection();
        String sql = "UPDATE pa_record SET record_name=?, record_date=?, record_memo=?, record_flag=? WHERE record_id=?";
        
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setString(1, model.getRecordName());
            pstm.setDate(2, new Date(model.getRecordDate().getTime()));
            pstm.setString(3, model.getRecordMemo());
            pstm.setString(4, model.getRecordFlag());
            pstm.setLong(5, model.getRecordId());

            return pstm.executeUpdate() > 0 ? "Ok" : "Err";
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
    }

    @Override
    public String delete(Record model) {
        return deleteById(model.getRecordId());
    }

    @Override
    public String deleteById(Long cid) {
        Connection conn = db.getConnection();
        String sql = "DELETE FROM pa_record WHERE record_id=?";
        
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setLong(1, cid);
            return pstm.executeUpdate() > 0 ? "Ok" : "Err";
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        } 
    }

    @Override
    public List<Record> getAllData(Record model) {
        List<Record> list = new ArrayList<>();
        Connection conn = db.getConnection();
        StringBuilder sql = new StringBuilder("SELECT * FROM pa_record WHERE 1=1 ");
        
        if(model.getRecordName() != null && !model.getRecordName().isEmpty()) {
            sql.append("AND record_name LIKE '%").append(model.getRecordName()).append("%' ");
        }
        if(model.getRecordFlag() != null && !model.getRecordFlag().isEmpty()) {
            sql.append("AND record_flag='").append(model.getRecordFlag()).append("' ");
        }
        sql.append("ORDER BY record_stmp DESC");
        
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql.toString())) {
            while(rs.next()) {
                Record record = new Record();
                record.setRecordId(rs.getLong("record_id"));
                record.setRecordName(rs.getString("record_name"));
                record.setRecordDate(rs.getDate("record_date"));
                record.setRecordMemo(rs.getString("record_memo"));
                record.setRecordPeople(rs.getString("record_people"));
                record.setRecordFlag(rs.getString("record_flag"));
                list.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Record findById(Long cid) {
        Connection conn = db.getConnection();
        String sql = "SELECT * FROM pa_record WHERE record_id=?";
        
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setLong(1, cid);
            ResultSet rs = pstm.executeQuery();
            if(rs.next()) {
                Record record = new Record();
                record.setRecordId(rs.getLong("record_id"));
                record.setRecordName(rs.getString("record_name"));
                record.setRecordDate(rs.getDate("record_date"));
                record.setRecordMemo(rs.getString("record_memo"));
                record.setRecordPeople(rs.getString("record_people"));
                record.setRecordFlag(rs.getString("record_flag"));
                return record;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Record find(Record model) {
        return findById(model.getRecordId());
    }

    @Override
    public boolean isSame(Record model, boolean sflag) {
        return false;
    }

    @Override
    public List<Record> getAllDataByPage(Record model, Page page) {
        List<Record> list = new ArrayList<>();
        Connection conn = db.getConnection();
        StringBuilder sql = new StringBuilder("SELECT * FROM pa_record WHERE 1=1 ");
        
        if(model.getRecordName() != null && !model.getRecordName().isEmpty()) {
            sql.append("AND record_name LIKE '%").append(model.getRecordName()).append("%' ");
        }
        if(model.getRecordFlag() != null && !model.getRecordFlag().isEmpty()) {
            sql.append("AND record_flag='").append(model.getRecordFlag()).append("' ");
        }
        sql.append("ORDER BY record_stmp DESC LIMIT ?,?");
        
        try (PreparedStatement pstm = conn.prepareStatement(sql.toString())) {
            pstm.setInt(1, (page.getCurrentPage()-1)*page.getCount());
            pstm.setInt(2, page.getCount());
            ResultSet rs = pstm.executeQuery();
            while(rs.next()) {
                Record record = new Record();
                record.setRecordId(rs.getLong("record_id"));
                record.setRecordName(rs.getString("record_name"));
                record.setRecordDate(rs.getDate("record_date"));
                record.setRecordMemo(rs.getString("record_memo"));
                record.setRecordPeople(rs.getString("record_people"));
                record.setRecordFlag(rs.getString("record_flag"));
                list.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(Record model) {
        Connection conn = db.getConnection();
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM pa_record WHERE 1=1 ");
        
        if(model.getRecordName() != null && !model.getRecordName().isEmpty()) {
            sql.append("AND record_name LIKE '%").append(model.getRecordName()).append("%' ");
        }
        if(model.getRecordFlag() != null && !model.getRecordFlag().isEmpty()) {
            sql.append("AND record_flag='").append(model.getRecordFlag()).append("' ");
        }
        
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql.toString())) {
            if(rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Record> getAllky() {
        return getAllData(new Record());
    }
}